<?php
/* 1. Grafik Total Point Per Karyawan */
/* @by Freezy */

/* 
 * @param periodeStart (unix time) - 
 * @param periodeEnd (unix time) - 
 * jumlah total point karyawan 
 */
function rep_total_poin_karyawan($karyID, $periodeStart, $periodeEnd){
	$start = date("Y-m",$periodeStart);
	$end   = date("Y-m",$periodeEnd);
	$start = split('-',$start);
	$end = split('-',$end);
	$startYear = $start[0];
	$startMonth = $start[1];
	$endYear = $end[0];
	$endMonth = $end[1];
	$data = array();
	
	for($year=$startYear; $year<=$endYear; $year++){	
		$data = array_merge($data,  __rep_total_poin_karyawan($karyID, $startYear == $endYear? $startMonth : 12, $endMonth, $year) );
	}
	
	return $data;
}

/*
 * @param periodeStart (unix time) - 
 * @param periodeEnd (unix time) - 
 * @by freezy
 */
function rep_rata_departemen($departemenID, $periodeStart, $periodeEnd){
	$start = date("Y-m",$periodeStart);
	$end   = date("Y-m",$periodeEnd);
	$start = split('-',$start);
	$end = split('-',$end);
	$startYear = $start[0];
	$startMonth = $start[1];
	$endYear = $end[0];
	$endMonth = $end[1];
	$data = array();
	
	for($year=$startYear; $year<=$endYear; $year++){	
		$data = array_merge($data,  __rep_rata_departemen($departemenID, $startYear == $endYear? $startMonth : 12, $endMonth, $year) );
	}
	
	return $data;
}

function rep_rata_seluruh_poin($startPeriode, $endPeriode){
	$data = array();
	$start = split('-', date("Y-m",$startPeriode));
	$end = split('-', date("Y-m",$endPeriode));
	$startYear = (int)$start[0];
	$startMonth = (int)$start[1];
	$endYear = (int)$end[0];
	$endMonth = (int)$end[1];

	for($year=$startYear; $year<=$endYear; $year++){	
		if ($startYear == $endYear){
			$data = array_merge($data, __rep_rata_seluruh_poin($startMonth, $endMonth, $year) );
		}
		else {
			$data = array_merge($data, __rep_rata_seluruh_poin($startMonth, 12, $year) );
			$startMonth = 1;
		}
	}
	
	return $data;
}

function rep_countRewardPunishment($deptID, $startPeriode, $endPeriode){
	$startPeriode = date("Y-m-d",$startPeriode);
	$endPeriode = date("Y-m-d",$endPeriode);
	$data = array();
	foreach($deptID as $deptIDD){
		$result = "SELECT a.PERB_TGL, a.KODE_KARYAWAN, b.NAMA_KARYAWAN, e.NAMA_DEPARTMENT, a1.DETACHIEV_ID, 
				f.ACHIEV_JENIS, f.ACHIEV_BIDANG, g.DETACHIEV_TINGKAT, g.DETACHIEV_NAMA,
				a1.DEPERB_COUNT, 
					(SELECT NILAI_POINT 
					 FROM nilai as xx 
					 WHERE xx.DETACHIEV_ID = a1.DETACHIEV_ID AND 
							a1.DEPERB_COUNT BETWEEN xx.NILAI_MIN AND xx.NILAI_MAX
					) AS POINT
			FROM perbuatan as a, detil_perbuatan as a1, 
				data_karyawan as b, relasi_div_jab_din as c, 
				dep_divisi_jabatan as d, data_department as e,
				prestasipelanggaran as f, detil_prestasi_pelanggaran as g
			WHERE a.PERB_NO = a1.PERB_NO AND
				a1.DEPERB_STATUS='2' AND 
				a.KODE_KARYAWAN = b.KODE_KARYAWAN AND
				b.KODE_KARYAWAN  = c.KODE_KARYAWAN AND
				c.ID_DEP_DIV_JAB = d.ID_DEP_DIV_JAB AND
				d.ID_DEPARTMENT  = e.ID_DEPARTMENT AND
				f.ACHIEV_ID = g.ACHIEV_ID AND 
				g.DETACHIEV_ID = a1.DETACHIEV_ID AND
				e.ID_DEPARTMENT  = '$deptIDD' AND 
				DATE_FORMAT( a.PERB_TGL, '%Y-%m' ) 
					BETWEEN DATE_FORMAT('$startPeriode', '%Y-%m' ) AND DATE_FORMAT('$endPeriode', '%Y-%m') 
			ORDER BY NAMA_KARYAWAN, ACHIEV_BIDANG, DEPERB_COUNT";
		//echo $result;
		$result = mysql_query($result);
		
		while ($row = mysql_fetch_assoc($result)){
				//echo $row['ACHIEV_JENIS']."<br>";
				if (strtolower($row['ACHIEV_JENIS']) === 'prestasi') 	$data[$deptIDD]["prestasi"]++;
				if (strtolower($row['ACHIEV_JENIS']) === 'pelanggaran') $data[$deptIDD]["pelanggaran"]++;
				//$avgcounter++;
		}
		
		//normalize data
		if (!$data[$deptIDD]["prestasi"]) 		$data[$deptIDD]["prestasi"] = 0;
		if (!$data[$deptIDD]["pelanggaran"]) 	$data[$deptIDD]["pelanggaran"] = 0;
		
		//hitung jumlah karyawannya
		$result2 = "SELECT a.KODE_KARYAWAN
			FROM perbuatan as a, detil_perbuatan as a1, 
				data_karyawan as b, relasi_div_jab_din as c, 
				dep_divisi_jabatan as d, data_department as e,
				prestasipelanggaran as f, detil_prestasi_pelanggaran as g
			WHERE a.PERB_NO = a1.PERB_NO AND
				a1.DEPERB_STATUS='2' AND 
				a.KODE_KARYAWAN = b.KODE_KARYAWAN AND
				b.KODE_KARYAWAN  = c.KODE_KARYAWAN AND
				c.ID_DEP_DIV_JAB = d.ID_DEP_DIV_JAB AND
				d.ID_DEPARTMENT  = e.ID_DEPARTMENT AND
				f.ACHIEV_ID = g.ACHIEV_ID AND 
				g.DETACHIEV_ID = a1.DETACHIEV_ID AND
				e.ID_DEPARTMENT  = '$deptIDD' AND 
				DATE_FORMAT( a.PERB_TGL, '%Y-%m' ) 
					BETWEEN DATE_FORMAT('$startPeriode', '%Y-%m' ) AND DATE_FORMAT('$endPeriode', '%Y-%m') 
			GROUP BY a.KODE_KARYAWAN";
		//echo $result2;
		$result2 = mysql_query($result2);
		$avgcounter = 0;
		while ($tt = mysql_fetch_assoc($result2)){ $avgcounter++; }
		
		//debug
		//echo "pres : ".$data[$deptIDD]["prestasi"]."<br>";
		//echo "pela : ".$data[$deptIDD]["pelanggaran"]."<br>";
		//echo "devi : ".$avgcounter;
		$data[$deptIDD]["prestasi"] 	= $avgcounter>0? $data[$deptIDD]["prestasi"]/$avgcounter : 0;
		$data[$deptIDD]["pelanggaran"] 	= $avgcounter>0? $data[$deptIDD]["pelanggaran"]/$avgcounter : 0;
	}
	return $data;
}

function rep_totalPelanggaranDepartemen($detachievID, $startPeriode, $endPeriode){
	$data = array();
	foreach($detachievID as $dd){
		$result = mysql_query(
					"SELECT a.KODE_KARYAWAN, d.DETACHIEV_ID, c.ACHIEV_ID, c.ACHIEV_JENIS
					FROM perbuatan AS a, detil_perbuatan AS b, prestasipelanggaran AS c, 
						detil_prestasi_pelanggaran AS d
					WHERE a.PERB_NO = b.PERB_NO
						AND b.DEPERB_STATUS='2'
						AND b.DETACHIEV_ID = d.DETACHIEV_ID
						AND d.ACHIEV_ID = c.ACHIEV_ID
						AND DATE_FORMAT(a.PERB_TGL, '%Y-%m') 
							BETWEEN DATE_FORMAT('$startPeriode', '%Y-%m' ) AND DATE_FORMAT('$endPeriode', '%Y-%m') 
						AND b.DETACHIEV_ID='$dd'");
		
		//hitung jumlah prestasi & pelanggaran
		while ($tmp = mysql_fetch_assoc($result) ){
			$data[$dd]++;
		}
	}
	return $data;
}

function rep_prestasiPelanggaranPerBidang($achievBidang, $startPeriode, $endPeriode){
	$data = array();
	foreach($achievBidang as $bidang){
		$result = mysql_query(
					"SELECT a.KODE_KARYAWAN, d.DETACHIEV_ID, c.ACHIEV_ID, c.ACHIEV_JENIS, c.ACHIEV_BIDANG
					FROM perbuatan AS a, detil_perbuatan AS b, prestasipelanggaran AS c, 
						detil_prestasi_pelanggaran AS d
					WHERE a.PERB_NO = b.PERB_NO
						AND b.DEPERB_STATUS='2'
						AND b.DETACHIEV_ID = d.DETACHIEV_ID
						AND d.ACHIEV_ID = c.ACHIEV_ID
						AND DATE_FORMAT(a.PERB_TGL, '%Y-%m') 
							BETWEEN DATE_FORMAT('$startPeriode', '%Y-%m' ) AND DATE_FORMAT('$endPeriode', '%Y-%m') 
						AND c.ACHIEV_BIDANG='$bidang'");
		
		//hitung jumlah prestasi & pelanggaran
		$avgcounter = 0;
		while ($tmp = mysql_fetch_assoc($result) ){
			if (strtolower($tmp['ACHIEV_JENIS']) === 'prestasi')	$data[$bidang]["prestasi"]++;
			if (strtolower($tmp['ACHIEV_JENIS']) === 'pelanggaran') $data[$bidang]["pelanggaran"]++;
			$avgcounter++;
		}
		
		if (!$data[$bidang]["prestasi"]) $data[$bidang]["prestasi"] = 0;
		if (!$data[$bidang]["pelanggaran"]) $data[$bidang]["pelanggaran"] = 0;
		
		//hitung avgnya
		$data[$bidang]["prestasi"] = $avgcounter>0? $data[$bidang]["prestasi"]/$avgcounter : 0;
		$data[$bidang]["pelanggaran"] = $avgcounter>0? $data[$bidang]["pelanggaran"]/$avgcounter : 0;
	}
	return $data;
}

//sumbu x nama departemen
function rep_rata_departemen2($departemenID, $periodeStart, $periodeEnd){
	$start = date("Y-m-d",$periodeStart);
	$end   = date("Y-m-d",$periodeEnd);
	$data = array();
	
	$data = array_merge($data,  __rep_rata_departemen2($departemenID, $start, $end) );
	return $data;
}

//sumbu x nama departemen
function rep_rata_seluruh_poin2($periodeStart, $periodeEnd){
	$start = date("Y-m-d",$periodeStart);
	$end   = date("Y-m-d",$periodeEnd);
	$data = array();
	
	$data = __rep_rata_seluruh_poin2($start, $end);
	return $data;
}











function __rep_countRewardPunishmentAll($startMonth, $endMonth, $year){
	$data = array();
	
	for($month=$startMonth; $month<=$endMonth; $month++){
		
		//hitung jumlah prestasi & pelanggaran
		$result = mysql_query(
				"SELECT a.KODE_KARYAWAN, d.DETACHIEV_ID, 
						c.ACHIEV_ID, c.ACHIEV_JENIS
				FROM perbuatan AS a, detil_perbuatan AS b, prestasipelanggaran AS c, 
					detil_prestasi_pelanggaran AS d
				WHERE a.PERB_NO = b.PERB_NO
					AND b.DEPERB_STATUS='2'
					AND b.DETACHIEV_ID = d.DETACHIEV_ID
					AND d.ACHIEV_ID = c.ACHIEV_ID
					AND year(a.PERB_TGL)='$year' AND month(a.PERB_TGL)='$month'");
		
		while ($tmp = mysql_fetch_assoc($result) ){
			if (strtolower($tmp['ACHIEV_JENIS']) === 'prestasi') 	$data["$month-$year"]['prestasi']++;
			if (strtolower($tmp['ACHIEV_JENIS']) === 'pelanggaran') $data["$month-$year"]['pelanggaran']++;
		}
		
		//normalize, klo ndak ada data
		if (!$data["$month-$year"]){
			$data["$month-$year"]['prestasi'] = 0;
			$data["$month-$year"]['pelanggaran'] = 0;	
		}
	
		//hitung jumlah karyawannya
		$devider = mysql_query(
				"SELECT a.KODE_KARYAWAN
				FROM perbuatan AS a, detil_perbuatan AS b, prestasipelanggaran AS c, 
					detil_prestasi_pelanggaran AS d
				WHERE a.PERB_NO = b.PERB_NO
					AND b.DEPERB_STATUS='2'
					AND b.DETACHIEV_ID = d.DETACHIEV_ID
					AND d.ACHIEV_ID = c.ACHIEV_ID
					AND year(a.PERB_TGL)='$year' AND month(a.PERB_TGL)='$month'
				GROUP BY KODE_KARYAWAN");
	
		while ($tmp = mysql_fetch_assoc($devider) ){ $data["$month-$year"]['karyCount']++; }
		if ( !$data["$month-$year"]['karyCount'] )  $data["$month-$year"]['karyCount']=0;
	}
	
	return $data;
}

function rep_countRewardPunishmentAll($startPeriode, $endPeriode){
	$data = array();
	
	$startMonth = (int)date("m", $startPeriode);
	$startYear 	= (int)date("Y", $startPeriode);
	$endMonth 	= (int)date("m", $endPeriode);
	$endYear 	= (int)date("Y", $endPeriode);

	//hitung reward punishment
	for($year=$startYear; $year<=$endYear; $year++){	
		
		$part = __rep_countRewardPunishmentAll($startMonth, ($startYear == $endYear? $endMonth : 12) , $year);
		$startMonth = $startYear == $endYear? $startMonth : 1;
		
		//make it flat... (normalize)
		foreach(array_keys($part) as $pp){
			$data[$pp]['prestasi'] = $part[$pp]['karyCount']? $part[$pp]['prestasi']/$part[$pp]['karyCount'] : 0;
			$data[$pp]['pelanggaran'] = $part[$pp]['karyCount']? $part[$pp]['pelanggaran']/$part[$pp]['karyCount'] : 0;
		}
	}
	
	return $data;
}